package com.zdkj.subway.check.util;

import com.zdkj.common.core.util.SpringContextHolder;
import com.zdkj.subway.check.mapper.PublicCommonMapper;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {


    /**
     * 需要执行的sql
     * @param sql 需要执行的sql
     * @param fields 需要获取的字段
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>> exeSqlQuery(String sql,String fieldstr) throws SQLException {
        String[] fields = fieldstr.split(",");
//        DataSource dataSource= SpringContextHolder.getBean(DataSource.class);
        JdbcTemplate jdbc = SpringContextHolder.getBean(JdbcTemplate.class);
        List<Map<String, Object>> maps = jdbc.queryForList(sql);
//        Connection connection=null;
//        Statement statement=null;
//        ResultSet resultSet =null;
//        List<Map<String,Object>> maps=new ArrayList<>();
//        try {
//            connection = dataSource.getConnection();
//            statement = connection.createStatement();
//            resultSet = statement.executeQuery(sql);
//            while(resultSet.next()){
//                Map<String,Object> map=new HashMap<>();
//                for(String f:fields){
//                    Object object = resultSet.getObject(f);
//                    map.put(f,object);
//                }
//                maps.add(map);
//            }
//        }finally {
//            if(resultSet!=null){
//                resultSet.close();
//            }
//            if(statement!=null){
//                statement.close();
//            }
//            if(connection!=null){
//                connection.close();
//            }
//        }
        return maps;
    }

    /**
     * 需要执行的sql
     * @param sql 需要执行的sql
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>> exeSqlQuery(String sql) throws SQLException {
//        DataSource dataSource= SpringContextHolder.getBean(DataSource.class);
//        Connection connection=null;
//        Statement statement=null;
//        ResultSet resultSet=null;
//        List<Map<String,Object>> maps=new ArrayList<>();
//        try {
//            connection = dataSource.getConnection();
//            statement = connection.createStatement();
//            resultSet = statement.executeQuery(sql);
//            List<String> fields=new ArrayList<>();
//            ResultSetMetaData metaData = resultSet.getMetaData();
//            int columnCount = metaData.getColumnCount();
//            for(int i=1;i<=columnCount;i++){
//                String columnName = metaData.getColumnLabel(i);//别名
//                if(StringUtils.isEmpty(columnName)){
//                    columnName = metaData.getColumnName(i); //字段名
//                }
//                fields.add(columnName);
//            }
//
//            while(resultSet.next()){
//                Map<String,Object> map=new HashMap<>();
//                for(String f:fields){
//                    Object object = resultSet.getObject(f);
//                    map.put(f,object);
//                }
//                maps.add(map);
//            }
//        }finally {
//            if(resultSet!=null){
//                resultSet.close();
//            }
//            if(statement!=null){
//                statement.close();
//            }
//            if(connection!=null){
//                connection.close();
//            }
//        }
        JdbcTemplate jdbc = SpringContextHolder.getBean(JdbcTemplate.class);
        List<Map<String, Object>> maps = jdbc.queryForList(sql);
        return maps;
    }


    /**
     * 需要执行的sql
     * @param sql 需要执行的sql
     * @return
     * @throws SQLException
     */
    public static Integer exeSqlCrud(String sql) throws SQLException {
//        DataSource dataSource= SpringContextHolder.getBean(DataSource.class);
//        Connection connection=null;
//        Statement statement=null;
//        try {
//            connection = dataSource.getConnection();
//            statement = connection.createStatement();
//            int i = statement.executeUpdate(sql);
//            return i;
//        }finally {
//            if(statement!=null){
//                statement.close();
//            }
//            if(connection!=null){
//                connection.close();
//            }
//        }
        JdbcTemplate jdbc = SpringContextHolder.getBean(JdbcTemplate.class);
        int update = jdbc.update(sql);
        return update;
    }


    /**
     * 需要执行的sql,通过连接获取
     * @param sql 需要执行的sql
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>> exeSqlQueryByCon(String sql) throws SQLException {
        DataSource dataSource= SpringContextHolder.getBean(DataSource.class);
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        List<Map<String,Object>> maps=new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            List<String> fields=new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for(int i=1;i<=columnCount;i++){
                String columnName = metaData.getColumnLabel(i);//别名
                if(StringUtils.isEmpty(columnName)){
                    columnName = metaData.getColumnName(i); //字段名
                }
                fields.add(columnName);
            }

            while(resultSet.next()){
                Map<String,Object> map=new HashMap<>();
                for(String f:fields){
                    Object object = resultSet.getObject(f);
                    map.put(f,object);
                }
                maps.add(map);
            }
        }finally {
            if(resultSet!=null){
                resultSet.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        return maps;
    }


}
